# encoding: utf-8
# @author: w4dll
# @file: register_pane.py
# @time: 2020/4/18 23:31

from PyQt5.Qt import*
from resource.scoreUi import Ui_Form
from mytool import sqlite3Class
from openpyxl import  workbook

class scoreTongji(QWidget, Ui_Form):

    def __init__(self, parent = None, *args, **kwargs):
        super().__init__(parent, *args, **kwargs)
        self.setAttribute(Qt.WA_StyledBackground, True)
        self.setupUi(self)
        self.db = sqlite3Class("data.db")

        self.initUi()

    def initUi(self):
        # 初始化tableview
        self.list_model = QStandardItemModel()
        self.list_model.setHorizontalHeaderLabels(["用户名","姓名", "一级单位", "二级单位", "三级单位",
                                                    "试卷名称", "考试时间", "用时", "题目数量",
                                                    "总分", "得分", "考试类别"])
        data = self.db.selectdata("select t.用户名, u.姓名, u.一级单位, u.二级单位, u.三级单位, t.试卷名称, t.考试时间, "
                                  "t.考试用时, t.题型数量, t.题型分值, t.总分, t.考试方式 from t_testresults as t join t_userinfo as u on t.用户名=u.用户名")
        for x in data:
            ls_q = []
            for y in x:
                q1 = QStandardItem(str(y))
                q1.setTextAlignment(Qt.AlignCenter)
                ls_q.append(q1)
            self.list_model.appendRow(ls_q)
        self.tableView.setModel(self.list_model)
        self.tableView.horizontalHeader().setStretchLastSection(True)
        # self.tableView.horizontalHeader().setSectionResizeMode(QHeaderView.Stretch)
        self.tableView.setSelectionBehavior(QAbstractItemView.SelectRows)  # 设置表格

    def save_excel(self):
        filepath, _ = QFileDialog.getSaveFileName(self, "导出题库到Excel文件", "./", "excel(*.xlsx)")
        if filepath == "":
            return

        self.tableView.selectAll()
        indexs = self.tableView.selectionModel().selection().indexes()
        if len(indexs) > 0:
            ls0 = []
            for i in range(0, len(indexs), 13):
                ls = []
                for j in range(13):
                    ls.append(indexs[i+j].data())
                ls0.append(ls)

            wb = workbook.Workbook()
            s = ["用户名","姓名", "一级单位", "二级单位", "三级单位", "试卷名称", "考试时间", "用时", "题目数量",
                 "总分", "得分", "考试类别"]
            ls0.insert(0, s)
            for x in ls0:
                wb.worksheets[0].append(x)

            wb.save(filepath)
            QMessageBox.about(self, "提示", "导出成功！")

if __name__ == '__main__':
    import sys
    app = QApplication(sys.argv)
    window = scoreTongji()
    window.show()
    sys.exit(app.exec_())